﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class CmsArticle : ICmsArticle
    {

        public bool Insert(CmsArticleModel article)
        {
            bool re = false;
            string insertsql = "insert into cms_article(columnid,title,content,addtime,addusername,starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,docstate,auditingman,auditingtime,auditingstate,modifytime,modifyusername,hits,doKnow,recommend,isbanner,parentename,childenamel) "
                + "values(" + article.Columnid + ",@title,@content,getDate(),'" + article.Addusername + "'," + (article.Starttime.Year < 1900 ? "null" : "'" + article.Starttime.ToString("yyyy-MM-dd") + "'") + "," + (article.Endtime.Year < 1900 ? "null" : "'" + article.Endtime.ToString("yyyy-MM-dd") + "'")
                + ",@articletype,@titleimg,@author,@authoremail,@indextop,@sorttop,@istouch,@templateid,@summary,@source,@sourceurl,@linkpath,@tag,@docstate,@auditingman," + (article.Auditingtime.Year < 1900 ? "null" : "'" + article.Auditingtime.ToString("yyyy-MM-dd HH:mm:ss") + "'") + ",@auditingstate,getDate(),@modifyusername,0,@doKnow,@recommend,@isbanner,@parentename,@childenamel);select @@IDENTITY";
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@title", SqlDbType.VarChar, 255),
                new SqlParameter("@content", SqlDbType.Text),
                new SqlParameter("@articletype", SqlDbType.Char,1),
                new SqlParameter("@titleimg",SqlDbType.VarChar,255),
                new SqlParameter("@author", SqlDbType.VarChar,50),
                new SqlParameter("@authoremail", SqlDbType.VarChar, 255),
                new SqlParameter("@indextop",SqlDbType.Char,1),
                new SqlParameter("@sorttop", SqlDbType.Char,1),
                new SqlParameter("@istouch", SqlDbType.Char,1),
                new SqlParameter("@templateid", SqlDbType.Int,4),
                new SqlParameter("@summary", SqlDbType.VarChar,500),
                new SqlParameter("@source", SqlDbType.VarChar,255),
                new SqlParameter("@sourceurl", SqlDbType.VarChar, 255),
                new SqlParameter("@linkpath", SqlDbType.VarChar, 100),
                new SqlParameter("@tag", SqlDbType.VarChar, 200),
                new SqlParameter("@docstate", SqlDbType.Char, 1),
                new SqlParameter("@auditingman", SqlDbType.VarChar, 50),
                new SqlParameter("@auditingstate",SqlDbType.Char, 1),
                new SqlParameter("@modifyusername", SqlDbType.VarChar, 50),
                new SqlParameter("@doKnow",SqlDbType.NVarChar,50),
                new SqlParameter("@recommend",SqlDbType.NVarChar,50),
                new SqlParameter("@isbanner",SqlDbType.NVarChar,50),
                new SqlParameter("@parentename",SqlDbType.NVarChar,50),
                new SqlParameter("@childenamel",SqlDbType.NVarChar,50)

            };
            parms[0].Value = article.Title;
            parms[1].Value = article.Content;
            parms[2].Value = article.Articletype;
            parms[3].Value = article.Titleimg;
            parms[4].Value = article.Author;
            parms[5].Value = article.Authoremail;
            parms[6].Value = article.Indextop;
            parms[7].Value = article.Sorttop;
            parms[8].Value = article.Istouch;
            parms[9].Value = article.Templateid;
            parms[10].Value = article.Summary;
            parms[11].Value = article.Source;
            parms[12].Value = article.Sourceurl;
            parms[13].Value = article.Linkpath;
            parms[14].Value = article.Tag;
            parms[15].Value = article.Docstate;
            parms[16].Value = article.Auditingman;
            parms[17].Value = article.Auditingstate;
            parms[18].Value = article.Modifyusername;
            parms[19].Value = article.DoKnow;
            parms[20].Value = article.Recommend;
            parms[21].Value = article.Isbanner;
            parms[22].Value = article.Parentename;
            parms[23].Value = article.Childenamel;
            //return Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, insertsql, new SqlParameter[0]).ToString());
             int i=SQLHelper.ExecuteSql(insertsql, parms);
            if (i > 0)
            {
                re = true;
            }
            return re;
        }
        //    int id = 0;
        //    using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
        //    {
        //        conn.Open();
        //        using (SqlTransaction trans = conn.BeginTransaction())
        //        {
        //            try
        //            {
        //                string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, insertsql, parms).ToString();

        //                id = int.Parse(s);
        //                if (id > 0)
        //                {
        //                    if (article.ExpertId > 0)
        //                    {
        //                        string INSERT_EXPERT_SQL = "insert into COFCO_EXPERT_ARTICLE(EXPERT_ID,ARTICLE_ID) values("+article.ExpertId+","+id+")";
        //                        int i = SQLHelper.ExecuteNonQuery(trans, CommandType.Text,INSERT_EXPERT_SQL , new SqlParameter[] { });
        //                        if (i > 0)
        //                        {
        //                            re = true;
        //                        }
        //                        else
        //                        {
        //                            trans.Rollback();
        //                        }
        //                    }
        //                }
        //                trans.Commit();
        //            }
        //            catch
        //            {
        //                trans.Rollback();
        //                throw;
        //            }
        //        }
        //    }

        //    return re;
        //}

        public bool Update(CmsArticleModel article)
        {
            bool re = false;
            string updatesql = "update cms_article set title=@title,content=@content,starttime=" + (article.Starttime.Year < 1900 ? "null" : "'" + article.Starttime.ToString("yyyy-MM-dd") + "'") + ""
                + ",endtime=" + (article.Endtime.Year < 1900 ? "null" : "'" + article.Endtime.ToString("yyyy-MM-dd") + "'") + ",articletype=@articletype,titleimg=@titleimg,author=@author,authoremail=@authoremail,indextop=@indextop,sorttop=@sorttop,istouch=@istouch,templateid=@templateid,summary=@summary,"
                + "source=@source,sourceurl=@sourceurl,linkpath=@linkpath,tag=@tag,docstate=@docstate,auditingman=@auditingman,auditingtime=" + (article.Auditingtime.Year < 1900 ? "null" : "'" + article.Auditingtime.ToString("yyyy-MM-dd HH:mm:ss") + "'") + ",auditingstate=@auditingstate,modifyusername=@modifyusername,modifytime=getDate(),doKnow=@doKnow,recommend=@recommend where articleid=" + article.Articleid;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@title", SqlDbType.VarChar, 255),
                new SqlParameter("@content", SqlDbType.Text),
                new SqlParameter("@articletype", SqlDbType.Char,1),
                new SqlParameter("@titleimg",SqlDbType.VarChar,255),
                new SqlParameter("@author", SqlDbType.VarChar,50),
                new SqlParameter("@authoremail", SqlDbType.VarChar, 255),
                new SqlParameter("@indextop",SqlDbType.Char,1),
                new SqlParameter("@sorttop", SqlDbType.Char,1),
                new SqlParameter("@istouch", SqlDbType.Char,1),
                new SqlParameter("@templateid", SqlDbType.Int,4),
                new SqlParameter("@summary", SqlDbType.VarChar,500),
                new SqlParameter("@source", SqlDbType.VarChar,255),
                new SqlParameter("@sourceurl", SqlDbType.VarChar, 255),
                new SqlParameter("@linkpath", SqlDbType.VarChar, 100),
                new SqlParameter("@tag", SqlDbType.VarChar, 200),
                new SqlParameter("@docstate", SqlDbType.Char, 1),
                new SqlParameter("@auditingman", SqlDbType.VarChar, 50),
                new SqlParameter("@auditingstate",SqlDbType.Char, 1),
                new SqlParameter("@modifyusername", SqlDbType.VarChar, 50),
                new SqlParameter("@doKnow",SqlDbType.NVarChar,50),
                new SqlParameter("@recommend",SqlDbType.NVarChar,50),



            };
            parms[0].Value = article.Title;
            parms[1].Value = article.Content;
            parms[2].Value = article.Articletype;
            parms[3].Value = article.Titleimg;
            parms[4].Value = article.Author;
            parms[5].Value = article.Authoremail;
            parms[6].Value = article.Indextop;
            parms[7].Value = article.Sorttop;
            parms[8].Value = article.Istouch;
            parms[9].Value = article.Templateid;
            parms[10].Value = article.Summary;
            parms[11].Value = article.Source;
            parms[12].Value = article.Sourceurl;
            parms[13].Value = article.Linkpath;
            parms[14].Value = article.Tag;
            parms[15].Value = article.Docstate;
            parms[16].Value = article.Auditingman;
            parms[17].Value = article.Auditingstate;
            parms[18].Value = article.Modifyusername;
            parms[19].Value = article.DoKnow;
            parms[20].Value = article.Recommend;
            int i = SQLHelper.ExecuteSql(updatesql, parms);
            if (i > 0)
            {
                SQLHelper.ExecuteSql("delete from COFCO_EXPERT_ARTICLE where ARTICLE_ID=" + article.Articleid, new SqlParameter[] { });
                if (article.ExpertId > 0)
                {
                    string INSERT_EXPERT_SQL = "insert into COFCO_EXPERT_ARTICLE(EXPERT_ID,ARTICLE_ID) values(" + article.ExpertId + "," + article.Articleid + ")";
                    SQLHelper.ExecuteSql(INSERT_EXPERT_SQL, new SqlParameter[] { });
                }
                re = true;
            }
            return re;
        }
        private const string GET_ARTICLE_SQL = "select articleid,columnid,title,content,addtime,addusername,starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,docstate,auditingman,auditingtime,auditingstate,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename,modifytime from cms_article where articleid=@articleid";
        public CmsArticleModel getCmsArticle(int aid)
        {
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@articleid", SqlDbType.Int, 4)
            };
            parms[0].Value = aid;
            CmsArticleModel model = null;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_ARTICLE_SQL, parms))
            {
                if (rdr.Read())
                {
                    model = new CmsArticleModel();
                    model.Articleid = rdr.GetInt32(0);
                    model.Columnid = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) model.Title = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) model.Content = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) model.Addtime = rdr.GetDateTime(4);
                    if (!rdr.IsDBNull(5)) model.Addusername = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) model.Starttime = rdr.GetDateTime(6);
                    if (!rdr.IsDBNull(7)) model.Endtime = rdr.GetDateTime(7);
                    if (!rdr.IsDBNull(8)) model.Articletype = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) model.Titleimg = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) model.Author = rdr.GetString(10);
                    if (!rdr.IsDBNull(11)) model.Authoremail = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) model.Indextop = rdr.GetString(12);
                    if (!rdr.IsDBNull(13)) model.Sorttop = rdr.GetString(13);
                    if (!rdr.IsDBNull(14)) model.Istouch = rdr.GetString(14);
                    if (!rdr.IsDBNull(15)) model.Templateid = rdr.GetInt32(15);
                    if (!rdr.IsDBNull(16)) model.Summary = rdr.GetString(16);
                    if (!rdr.IsDBNull(17)) model.Source = rdr.GetString(17);
                    if (!rdr.IsDBNull(18)) model.Sourceurl = rdr.GetString(18);
                    if (!rdr.IsDBNull(19)) model.Linkpath = rdr.GetString(19);
                    if (!rdr.IsDBNull(20)) model.Tag = rdr.GetString(20);
                    if (!rdr.IsDBNull(21)) model.Docstate = rdr.GetString(21);
                    if (!rdr.IsDBNull(22)) model.Auditingman = rdr.GetString(22);
                    if (!rdr.IsDBNull(23)) model.Auditingtime = rdr.GetDateTime(23);
                    if (!rdr.IsDBNull(24)) model.Auditingstate = rdr.GetString(24);
                    if (!rdr.IsDBNull(25)) model.Articletitle = rdr.GetString(25);
                    if (!rdr.IsDBNull(26)) model.Articlekeyword = rdr.GetString(26);
                    if (!rdr.IsDBNull(27)) model.Articledesc = rdr.GetString(27);
                    if (!rdr.IsDBNull(28)) model.Isstaticpub = rdr.GetString(28);
                    if (!rdr.IsDBNull(29)) model.Staticpagename = rdr.GetString(29);
                    if (!rdr.IsDBNull(30)) model.Modifytime = rdr.GetDateTime(30);

                    string expert_get_sql = "select EXPERT_ID from COFCO_EXPERT_ARTICLE where ARTICLE_ID=" + aid;
                    using (SqlDataReader rdr2 = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, expert_get_sql, new SqlParameter[] { }))
                    {
                        if (rdr2.Read())
                        {
                            model.ExpertId = rdr2.GetInt32(0);
                        }
                    }
                }
            }
            return model;
        }

        
        public IList getCmsArticleList(CmsArticleQuery caq)
        {
            string qsql = "select ca.articleid,ca.columnid,ca.title,"
                    + "ca.addtime,ca.addusername,ca.starttime,ca.author,ca.indextop,ca.sorttop,ca.istouch,"
                    + "ca.docstate,caf.hits,caf.articleid as aid"
                    +" from cms_article ca left join cms_article_front caf on ca.articleid=caf.articleid"
                    +" where ca.columnid=" + caq.columnid;
            if (caq.queryvalue != null && caq.queryvalue != "")
            {
                if (caq.querykey != null && caq.querykey != "")
                {
                    if (caq.querytype == "1")
                    {
                        qsql += " and ca." + caq.querykey + "='" + StringUtil.ReplaceSecStr(caq.queryvalue) + "'";
                    }
                    else
                    {
                        qsql += " and ca." + caq.querykey + " like '%" + StringUtil.ReplaceSecStr(caq.queryvalue) + "%'";
                    }
                }
                else
                {
                    if (caq.querytype == "1")
                    {
                        qsql += " and (ca.title='" + StringUtil.ReplaceSecStr(caq.queryvalue) + "' or ca.addusername = '" + StringUtil.ReplaceSecStr(caq.queryvalue) + "' or ca.author='" + StringUtil.ReplaceSecStr(caq.queryvalue) + "')";
                    }
                    else
                    {
                        qsql += " and (ca.title like '%" + StringUtil.ReplaceSecStr(caq.queryvalue) + "%' or ca.addusername like '%" + StringUtil.ReplaceSecStr(caq.queryvalue) + "%' or ca.author like '%" + StringUtil.ReplaceSecStr(caq.queryvalue) + "%')"; 
                    }
                }   
            }
            if (caq.docstate != null && caq.docstate != "")
            {
                qsql += " and ca.docstate='" + caq.docstate + "'";
            }
            qsql += " order by ca." + caq.ordername + " " + caq.ordertype;

            SqlParameter[] parms = new SqlParameter[] {
            };

            IList datalist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleModel model = new CmsArticleModel();
                    model.Articleid = rdr.GetInt32(0);
                    model.Columnid = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) model.Title = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) model.Addtime = rdr.GetDateTime(3);
                    if (!rdr.IsDBNull(4)) model.Addusername = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) model.Starttime = rdr.GetDateTime(5);
                    if (!rdr.IsDBNull(6)) model.Author = rdr.GetString(6);
                    
                    if (!rdr.IsDBNull(7)) model.Indextop = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) model.Sorttop = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) model.Istouch = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) model.Docstate = rdr.GetString(10);
                    if (!rdr.IsDBNull(11)) model.Hits = rdr.GetInt32(11);
                    if (!rdr.IsDBNull(12)) model.Isfront = true;
                    
                    
                    datalist.Add(model);
                }
            }
            return datalist;
        }

        public bool Del(string ids)
        {
            bool re = false;
            string delsql = "delete from cms_article where articleid in("+ids+")";
            int i = SQLHelper.ExecuteSql(delsql, new SqlParameter[]{});
            if (i > 0) re = true;
            SQLHelper.ExecuteSql("delete from COFCO_EXPERT_ARTICLE where ARTICLE_ID in (" + ids + ")", new SqlParameter[] { });
            return re;
        }

        private const string GET_ARTICLE_SEO_SQL = "select staticpagename,isstaticpub,articletitle,articlekeyword,articledesc,title from cms_article where articleid=@articleid";
        public CmsArticleModel GetCmsArticelSeo(int aid)
        {
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@articleid", SqlDbType.Int, 4)
            };
            parms[0].Value = aid;
            CmsArticleModel model = null;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_ARTICLE_SEO_SQL, parms))
            {
                if (rdr.Read())
                {
                    model = new CmsArticleModel();
                    if (!rdr.IsDBNull(0)) model.Staticpagename = rdr.GetString(0);
                    if (!rdr.IsDBNull(1)) model.Isstaticpub = rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) model.Articletitle = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) model.Articlekeyword = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) model.Articledesc = rdr.GetString(4);
                    model.Title = rdr.GetString(5);
                    
                }
            }
            return model;
        }

        public bool UpdateSeo(CmsArticleModel seo)
        {
            string usql = "update cms_article set staticpagename=@staticpagename,articletitle=@articletitle,articlekeyword=@articlekeyword,articledesc=@articledesc,isstaticpub=@isstaticpub where articleid=@articleid";
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@staticpagename", SqlDbType.VarChar, 100),
                new SqlParameter("@articletitle", SqlDbType.VarChar, 255),
                new SqlParameter("@articlekeyword",SqlDbType.VarChar, 500),
                new SqlParameter("@articledesc", SqlDbType.VarChar, 500),
                new SqlParameter("@articleid", SqlDbType.Int, 4),
                new SqlParameter("@isstaticpub",SqlDbType.Char ,1 )
            };
            parms[0].Value = seo.Staticpagename;
            
            parms[1].Value = seo.Articletitle;
            parms[2].Value = seo.Articlekeyword;
            parms[3].Value = seo.Articledesc;
            parms[4].Value = seo.Articleid;
            parms[5].Value = seo.Isstaticpub;

            int i = SQLHelper.ExecuteSql(usql, parms);
            if (i > 0) return true;
            return false;

        }

        public bool UpdateState(int aid, string state, string musername)
        {
            string usql = "update cms_article set docstate=@docstate,modifytime=getDate(),modifyusername=@modifyusername where articleid=@articleid";
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@docstate", SqlDbType.Char, 1),
                new SqlParameter("@articleid", SqlDbType.Int, 4),
                new SqlParameter("@modifyusername", SqlDbType.VarChar, 50)
            };
            parms[0].Value = state;
            parms[1].Value = aid;
            parms[2].Value = musername;

            int i = SQLHelper.ExecuteSql(usql, parms);
            if (i > 0) return true;
            return false;

        }

        public bool AudutArticle(int aid, string state, string musername)
        {
            string usql = "update cms_article set docstate=@docstate,auditingtime=getdate(),auditingman=@modifyusername,auditingstate=@auditingstate where articleid=@articleid";
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@docstate", SqlDbType.Char, 1),
                new SqlParameter("@articleid", SqlDbType.Int, 4),
                new SqlParameter("@modifyusername", SqlDbType.VarChar, 50),
                new SqlParameter("@auditingstate", SqlDbType.Char, 1)
            };
            parms[0].Value = state;
            parms[1].Value = aid;
            parms[2].Value = musername;
            parms[3].Value = state;
            int i = SQLHelper.ExecuteSql(usql, parms);
            if (i > 0) return true;
            return false;
        }

        public static IList getCommunicateIList(string sql)
        {
            IList piclist = new List<CmsArticleModel>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    piclist.Add(OgilvyOne.Utility.Util.getObject(new CmsArticleModel(), reader) as CmsArticleModel);
                }
            }
            return piclist;
        }
        public int getTotal(string sql)
        {
            return (int)SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, new SqlParameter[0]);
        }
    }
}
